
USE [ppjdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[svc].[uspGetStoreIPListInfo]') AND type in (N'P', N'PC'))
DROP PROCEDURE [svc].[uspGetStoreIPListInfo]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*
  -----------------------------------------------------------------------
  File Name		:
  Description	:	Returns Store data from dbo.tblStore table base 
	on value of @biStoreIPListID parameter
  Author        :   Sergey Morozov
  Copyright     :   (c) 2012 PPJ
  Incept		:	08/24/2012
  -----------------------------------------------------------------------
  Description/Purpose:

  Output values:
	@iRetCode =  0, sucess,
	@iRetCode = -1, SQL update error, detail info recorded into dbo.tblSQLAudit table
	@iRetCode = -2, Parameter @biStoreIPListID is NULL or zero
	@iRetCode = -3, Domain record for StoreIPList {0} not found in dbo.tblStoreIPList table
	@iRetCode = -4, Store for StoreIPList {0} has "revoke" status

  -----------------------------------------------------------------------
  Change Log:
	Author			Date         Change
  -----------------------------------------------------------------------
  Sergiy Morozov 08/24/2012		development
  
  -----------------------------------------------------------------------
  Sample Script
  -----------------------------------------------------------------------

	declare @biStoreIPListID [bigint]
		,@iRetCode [int]
		,@szMsgTier1 [nvarchar] (2048)
		,@szMsgTier2 [nvarchar] (1024)

	select @biStoreIPListID = 2

	exec [svc].[uspGetStoreIPListInfo]
		@biStoreIPListID
		,@iRetCode output
		,@szMsgTier1 output   
		,@szMsgTier2 output  

	select 	
		@iRetCode as [RetCode] 
		,@szMsgTier1 as [RetMsg1]   
		,@szMsgTier2 as [RetMsg2]   		

*/

CREATE PROCEDURE [svc].[uspGetStoreIPListInfo]
	@biStoreIPListID [bigint]
    ,@iRetCode [int] output
    ,@szMsgTier1 [nvarchar] (2048) output   
    ,@szMsgTier2 [nvarchar] (1024) output  
AS
BEGIN

SET NOCOUNT ON;

DECLARE @iIsProcessComplete int
, @szProcessName VARCHAR(50)
, @ErrorMessage nvarchar(2048) 
, @ErrorNumber int
, @ErrorSeverity int
, @ErrorState int
, @ErrorProcedure nvarchar(2048)
, @ErrorLine int
, @TranCounter int
, @RowCount int
, @Msg nvarchar(4000)
, @iJobSetID int
, @szInsertedBy varchar(16)
, @szErrSubst [varchar] (100)

-- Get current stored proc name
SELECT @szProcessName = ISNULL(OBJECT_NAME(@@PROCID), 'uspGetStoreIPListInfo')

BEGIN TRY
-----------------------------------
--BEGIN WORK
-----------------------------------

select @iRetCode = 0
	,@szMsgTier1 =''
	,@szMsgTier2=''
	,@szErrSubst = ''

declare @bTmpStoreRevokeStatus [bit]
	,@biStoreID [bigint]

declare @tbl table (
	StoreIPListID [bigint] not null,
	StoreID [bigint] not null,
	IPAddress [varchar] (64) not null,
	DomainName [nvarchar] (128) not null,
	TimerValue1 [int] not null,
	TimerValue2 [int] not null,
	RevokeStatus [bit] not null,
	DateRevoked [datetime] null,
	DateCreated [datetime] null,
	DateUpdated [datetime] null)
	


-- Validate parameters
if coalesce(@biStoreIPListID,0)=0
-- Parameter is NULL or zero
	set @iRetCode = -2
else 	
 begin
 
	insert into @tbl (
		StoreIPListID,
		StoreID,
		IPAddress,
		DomainName,
		TimerValue1,
		TimerValue2,
		RevokeStatus,
		DateRevoked,
		DateCreated,
		DateUpdated)
	select 
		StoreIPListID,
		StoreID,
		IPAddress,
		DomainName,
		TimerValue1,
		TimerValue2,
		RevokeStatus,
		DateRevoked,
		DateCreated,
		DateUpdated
		from dbo.tblStoreIPList (nolock) 
		where StoreIPListID = @biStoreIPListID
	
	if @@ROWCOUNT = 0 
		select @iRetCode = -3
			,@szErrSubst = CAST(@biStoreIPListID as [varchar] (30))
	else 
	 begin
		select @bTmpStoreRevokeStatus = s.RevokeStatus 
			from dbo.tblStore s (nolock) 
			inner join @tbl t on t.StoreID = s.StoreID

		-- Store has "revoke" status	
		if coalesce(@bTmpStoreRevokeStatus,0)=1
			select @iRetCode = -4
				,@szErrSubst = CAST(@biStoreIPListID as [varchar] (30))
			
	 end
 end

if @iRetCode = 0
 begin
 
	select 
		StoreIPListID,
		StoreID,
		IPAddress,
		DomainName,
		TimerValue1,
		TimerValue2,
		RevokeStatus,
		DateRevoked,
		DateCreated,
		DateUpdated
		from @tbl 
	
 end
else
 begin
	select @szMsgTier1=replace(t.MsgTier1,'{0}',''''+@szErrSubst+''''),
		@szMsgTier2=replace(u.MsgTier2,'{0}',''''+@szErrSubst+''''),
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode
 end

 RETURN 0
END TRY
BEGIN CATCH
-- If failed rollback
-- get extended error information
	select @ErrorNumber  = ERROR_NUMBER(), @ErrorSeverity  = ERROR_SEVERITY(), @ErrorState = ERROR_STATE()
	, @ErrorProcedure  = ERROR_PROCEDURE(), @ErrorLine  = ERROR_LINE(), @ErrorMessage  = ERROR_MESSAGE()
	set @Msg = '; ErrorNumber = ' + cast(@ErrorNumber as varchar(4))
	+ ', ErrorSeverity  = '       + cast(@ErrorSeverity as varchar(4))
	+ ', ErrorState = '                 + cast(@ErrorState as varchar(4))
	+ ', ErrorProcedure = '       + cast(@ErrorProcedure as varchar(128))
	+ ', ErrorLine = '                  + cast(@ErrorLine as varchar(4))
	+ ', ErrorMessage = '         + cast(@ErrorMessage as varchar(2048))

	IF @TranCounter is not null
	 begin
		IF @TranCounter = 0 -- Transaction started in procedure.
		 ROLLBACK TRANSACTION;
		ELSE IF XACT_STATE() <> -1 -- roll back to the savepoint
		 ROLLBACK TRANSACTION uspGetStoreIPListInfo;
	 end

	-- log error
	EXECUTE  dbo.uspRecordSQLAudit
	@biProcessID = -1
	,@iErrorNumber = @ErrorNumber
	,@iErrorSeverity = @ErrorSeverity
	,@iErrorState = @ErrorState
	,@szErrorProcedure = @ErrorProcedure
	,@iErrorLine = @ErrorLine
	,@szErrorMessage = @ErrorMessage
	,@szParameters = ''
	,@iInserted = 0

	select @iRetCode = -1 

	select @szMsgTier1=@ErrorMessage,
		@szMsgTier2=u.MsgTier2,
		@iRetCode= case when u.[UIErrCode] is null then @iRetCode else t.[UIErrCode] end
		from [dbo].[tblMsgOutput] t (nolock)
		left join [dbo].[tblUIMessage] u (nolock) on u.[UIErrCode] = t.[UIErrCode]
		where t.SPName = @szProcessName
		and t.RetCode = @iRetCode

RETURN -1
END CATCH

END
GO

if not exists (select 1 from dbo.tblMsgOutput (nolock) where SPName = 'uspGetStoreIPListInfo')
insert into dbo.tblMsgOutput (SPName,RetCode,MsgTier1,UIErrCode)
select 'uspGetStoreIPListInfo',-1, 'SQL update error',207
union all
select 'uspGetStoreIPListInfo',-2, 'Parameter @biStoreIPListID is NULL or zero',207
union all
select 'uspGetStoreIPListInfo',-3, 'No record found in dbo.tblStoreIPList for StoreIPListID {0}',207
union all
select 'uspGetStoreIPListInfo',-4, 'Store has "revoke" status for StoreIPListID {0}',207
go